package Demo0530;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Demo {
        // 数据库连接信息
        private static final String URL = "jdbc:mysql://localhost:3306/userdb";
        private static final String DB_USERNAME = "root";
        private static final String DB_PASSWORD = "123456";


        private static final String INSERT_SQL = "INSERT INTO employees(emp_id, emp_name, emp_password, emp_age) VALUES(?, ?, ?, ?)";
        private static final String SELECT_ALL_SQL = "SELECT * FROM employees";
        private static final String SELECT_BY_ID_SQL = "SELECT * FROM employees WHERE emp_id = ?";
        private static final String UPDATE_SQL = "UPDATE employees SET emp_name=?, emp_password=?, emp_age=? WHERE emp_id=?";
        private static final String DELETE_SQL = "DELETE FROM employees WHERE emp_id=?";

        private Connection getConnection() throws SQLException {
            return DriverManager.getConnection(URL, DB_USERNAME, DB_PASSWORD);
        }

        // 添加员工
        public boolean addEmp(Emp emp) {
            try (Connection conn = getConnection();
                 PreparedStatement pstmt = conn.prepareStatement(INSERT_SQL)) {

                pstmt.setInt(1, emp.getId());
                pstmt.setString(2, emp.getName());
                pstmt.setString(3, emp.getPassword());
                pstmt.setInt(4, emp.getAge());

                return pstmt.executeUpdate() > 0;
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
        }

        // 获取所有员工
        public List<Emp> getAllEmployees() {
            List<Emp> employees = new ArrayList<>();

            try (Connection conn = getConnection();
                 Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(SELECT_ALL_SQL)) {

                while (rs.next()) {
                    Emp emp = new Emp();
                    emp.setId(rs.getInt("emp_id"));
                    emp.setName(rs.getString("emp_name"));
                    emp.setPassword(rs.getString("emp_password"));
                    emp.setAge(rs.getInt("emp_age"));
                    employees.add(emp);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            return employees;
        }

        // 根据ID查询员工
        public Emp getEmployeeById(int id) {
            try (Connection conn = getConnection();
                 PreparedStatement pstmt = conn.prepareStatement(SELECT_BY_ID_SQL)) {

                pstmt.setInt(1, id);
                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next()) {
                        Emp emp = new Emp();
                        emp.setId(rs.getInt("emp_id"));
                        emp.setName(rs.getString("emp_name"));
                        emp.setPassword(rs.getString("emp_password"));
                        emp.setAge(rs.getInt("emp_age"));
                        return emp;
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }

        // 更新员工信息
        public boolean updateEmployee(Emp emp) {
            try (Connection conn = getConnection();
                 PreparedStatement pstmt = conn.prepareStatement(UPDATE_SQL)) {

                pstmt.setString(1, emp.getName());
                pstmt.setString(2, emp.getPassword());
                pstmt.setInt(3, emp.getAge());
                pstmt.setInt(4, emp.getId());

                return pstmt.executeUpdate() > 0;
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
        }

        // 删除员工
        public boolean deleteEmployee(int id) {
            try (Connection conn = getConnection();
                 PreparedStatement pstmt = conn.prepareStatement(DELETE_SQL)) {

                pstmt.setInt(1, id);
                return pstmt.executeUpdate() > 0;
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
        }
    }

